﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Threading;

namespace WebCrawling.Data.Extracting.DB
{
    public class MySqlDBUtil
    {
        public static string DTATBASENAME = "data";
        public static string[] FIELDS = new string[] { "url", "pagecontent"};
        public static string SCRAPURLTABLENAME = "scrapurl";
        public static string CRAWLURLTABLENAME = "crawlurl";
        public MySqlConfig config;
        private static String connectionString;
        private static string sync = "";
        private static MySqlConnection mySqlConnection = null;
        public string[] s;
        public MySqlDBUtil(MySqlConfig mySqlConfig, string tableName, String[] fields)
        {
            try
            {
                s = fields;
                string temp = "";
                for (int i = 0; i < s.Length; i++)
                {
                    temp = temp + s[i] + " LONGTEXT,";
                }
                temp = temp.Substring(0, temp.Length - 1);
                this.config = mySqlConfig;
                connectionString = "server=" + config.ServerName + ";user id=" + config.UserID + "; password=" + config.Password + ";pooling=false;";
                string myExecuteQuery = "create database if not exists " + config.DataBaseName + ";use " + config.DataBaseName + ";create table if not exists " + tableName + "(id int not null primary key auto_increment," + temp + ")default charset=utf8;";
                mySqlConnection = new MySqlConnection(connectionString);
                MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, mySqlConnection);
                if (myCommand.Connection.State == ConnectionState.Closed)
                {
                    myCommand.Connection.Open();
                }

                myCommand.ExecuteNonQuery();
                if (myCommand.Connection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
            }
            catch (System.Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }


        }

        public void close()
        {
            mySqlConnection.Close();
        }

        public void insert(string tableName, string[] fieldvalues)
        {
            string sql = "";
            try
            {
                string fields = "";
                string values = "";

                MySqlParameter[] parameters = new MySqlParameter[s.Length];
                for (int i = 0; i < s.Length; i++)
                {
                    parameters[i] = new MySqlParameter("?" + s[i], MySqlDbType.Text);
                    if (fieldvalues[i] == null)
                    {
                        fieldvalues[i] = "";
                    }
                    parameters[i].Value = fieldvalues[i].Replace("\"\"", string.Empty);
                    fields = fields + s[i] + ",";
                    values = values + "?" + s[i] + ",";
                }
                fields = fields.Substring(0, fields.Length - 1);
                values = values.Substring(0, values.Length - 1);
                sql = "use " + config.DataBaseName + ";insert into " + tableName + "(" + fields + ") select " + values + " from dual where not exists(select * from " + tableName + " where " + tableName + "." + s[0] + "='" + fieldvalues[0].Replace("'", "''") + "');";
                ExecuteInsert(sql, parameters);
            }
            catch (System.Exception e)
            {
                System.Console.WriteLine(sql);
                System.Console.WriteLine(e.Message);
            }

        }
        //执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
        public static void ExecuteInsert(string sql, MySqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            Monitor.Enter(sync);
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }
            MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
            try
            {
                if (parameters != null) cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }
            finally
            {
                if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
                Monitor.Pulse(sync);
                Monitor.Exit(sync);
            }

        }
        public void ExecuteInsert(string sql)
        {
            ExecuteInsert(sql, null);
        }
        public bool IsExist(string sql)
        {
            Monitor.Enter(sync);
            sql = "use " + config.DataBaseName + ";" + sql;
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }
            MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
            try
            {

                MySqlDataReader dataReader = cmd.ExecuteReader();
                return dataReader.HasRows;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }
            finally
            {
                if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
                Monitor.Pulse(sync);
                Monitor.Exit(sync);
            }

        }
        public string getFieldValue(string tableName, string conditionField, string conditionFieldValue, string returnField)
        {
            string html = "";
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }
            DataTable dt = new DataTable();

            //dt.DataSet.EnforceConstraints = false;
            string sql = "select " + returnField + " from " + tableName + "where " + conditionField + "='" + conditionFieldValue + "';";
            sql = "use " + config.DataBaseName + ";" + sql;
            MySqlCommand myCmd = new MySqlCommand(sql, mySqlConnection);
            MySqlDataReader reader = myCmd.ExecuteReader();
            while (reader.Read())
            {
                html = reader.GetString(0);
            }
            reader.Close();
            return html;
        }
        public List<string> getListData(string tablename, string returnField)
        {
            List<string> list = new List<string>();
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }
            DataTable dt = new DataTable();

            //dt.DataSet.EnforceConstraints = false;
            string sql = "select " + returnField + " from " + tablename + ";";
            sql = "use " + config.DataBaseName + ";" + sql;
            MySqlCommand myCmd = new MySqlCommand(sql, mySqlConnection);
            MySqlDataReader reader = myCmd.ExecuteReader();
            while (reader.Read())
            {
                list.Add(reader.GetString(0));
            }
            reader.Close();
            return list;
        }
        public DataSet getTableData(string tablename)
        {
            DataSet ds = new DataSet();
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }

            //dt.DataSet.EnforceConstraints = false;
            string sql = "select * from " + tablename + ";";
            sql = "use " + config.DataBaseName + ";" + sql;
            MySqlCommand myCmd = new MySqlCommand(sql, mySqlConnection);
            MySqlDataAdapter myA = new MySqlDataAdapter();
            myA.SelectCommand = myCmd;
            myA.Fill(ds);
            return ds;
        }
    }
}
